package com.lyf.scm.admin.domain.service.impl;

import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import com.github.pagehelper.PageInfo;
import com.lyf.scm.admin.api.dto.*;
import com.lyf.scm.admin.domain.remote.allocationservice.dto.ActualSalesQtyQueryDTO;
import com.lyf.scm.admin.domain.remote.allocationservice.facade.OperationLogRecordFacade;
import com.lyf.scm.admin.domain.remote.allocationservice.facade.OtherTargetGeneralHandlerFacade;
import com.lyf.scm.admin.domain.remote.allocationservice.facade.SalesPlanEsFacade;
import com.lyf.scm.admin.domain.remote.allocationservice.facade.SalesPlanEsLazyLoadingFacade;
import com.lyf.scm.admin.domain.remote.basedata.facade.BaseDataFacade;
import com.lyf.scm.admin.domain.remote.item.facade.ItemCoreFacade;
import com.lyf.scm.admin.domain.service.DemandPlanService;
import com.lyf.scm.admin.domain.service.PermissionService;
import com.lyf.scm.admin.domain.util.OtherTargetHandleUtil;
import com.lyf.scm.common.constants.CommonConstants;
import com.lyf.scm.common.enums.*;
import com.lyf.scm.common.model.ExcelExportDTO;
import com.lyf.scm.common.model.ExcelExportDataDTO;
import com.lyf.scm.common.model.SalesPlanSelectDTO;
import com.lyf.scm.common.util.AdvancedSearchUtil;
import com.lyf.scm.common.util.ExcelExportUtil;
import com.lyf.scm.common.util.date.MyDateUtil;
import com.lyf.scm.common.util.style.SalesPlanCellWriterHandler;
import com.rome.arch.core.exception.RomeException;
import com.rome.user.common.utils.UserContext;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.*;

/**
 * description:
 * 需求计划
 *
 * @author: 李兵兵
 * @data: 2020/8/7 17:25
 */
@Slf4j
@Service
public class SalesPlanServiceImpl implements DemandPlanService {

    @Resource
    private BaseDataFacade baseDataFacade;
    @Resource
    private ItemCoreFacade itemCoreFacade;
    @Resource
    private SalesPlanEsFacade salesPlanEsFacade;
    @Resource
    private PermissionService permissionService;
    @Resource
    private OperationLogRecordFacade operationLogRecordFacade;
    @Resource
    private SalesPlanEsLazyLoadingFacade salesPlanEsLazyLoadingFacade;
    @Resource
    private OtherTargetGeneralHandlerFacade otherTargetGeneralHandlerFacade;
    @Value("${filePath}")
    private String filePath;

    @Override
    public PlanResponseDTO<SalesPlanInfoDTO> listWeekBySelective(SalesPlanDTO salesPlanDTO) {
        // 校验登录用户渠道权限
        List<String> channelCodeList = this.checkChannelPermissions(salesPlanDTO.getChannelCodeList());
        salesPlanDTO.setChannelCodeList(channelCodeList);
        //获得最低级品类查询条件
//        List<SalesPlanSelectDTO> categorySalesPlanSelectDTOs = itemCoreFacade.getSubcategoryCode(salesPlanDTO.getCategoryCodeList());
        List<SalesPlanSelectDTO> areaSalesPlanSelectDTOs = baseDataFacade.getSubAreaCode(salesPlanDTO.getAreaCodeList());
        //赋值品类搜索条件
        EsAggregationDTO esAggregationDTO = this.setSearchFields(salesPlanDTO, salesPlanDTO.getCategoryCodeList(), areaSalesPlanSelectDTOs);
        List<Map<String, Date>> weekDates = MyDateUtil.getWeekDates(salesPlanDTO.getStartTime(), DataDisplayCycleEnum.WEEKLY_FOURTEEN.getValue());
        esAggregationDTO.getEsQuery().setStartDate(DateUtil.format(weekDates.get(0).get("startTime"), DatePattern.NORM_DATE_PATTERN));
        esAggregationDTO.getEsQuery().setEndDate(DateUtil.format(weekDates.get(weekDates.size() - 1).get("endTime"), DatePattern.NORM_DATE_PATTERN));
        esAggregationDTO.setPageNum(salesPlanDTO.getPageNum());
        esAggregationDTO.setPageSize(salesPlanDTO.getPageSize());
        esAggregationDTO.setTableCode(TableCodeEnum.SALE_PLAN.getCode());
        esAggregationDTO.setUserId(UserContext.getUserId());
        //获取起始十四周起始日期字符串集合
        List<String> dateList = new ArrayList<>();
        List<Date> dates = new ArrayList<>();
        for (Map<String, Date> item : weekDates) {
            dateList.add(DateUtil.format(item.get("startTime"), DatePattern.NORM_DATE_PATTERN));
            dates.add(item.get("startTime"));
        }
        esAggregationDTO.setDateList(dateList);
        PageInfo<EsReturnDTO> esReturnDTOS = salesPlanEsLazyLoadingFacade.pageQuery(esAggregationDTO);
        if (Objects.isNull(esReturnDTOS) || CollectionUtils.isEmpty(esReturnDTOS.getList())) {
            return null;
        }
        List<EsReturnDTO> result = esReturnDTOS.getList();
        PlanResponseDTO<SalesPlanInfoDTO> planResponse = new PlanResponseDTO();
        planResponse.setTotal(esReturnDTOS.getTotal());
        planResponse.setPageNum(esReturnDTOS.getPageNum());
        planResponse.setHasNextPage(esReturnDTOS.isHasNextPage());
        List<SalesPlanInfoDTO> pageList = new ArrayList<>();
        //TODO 临时将实际销量指标设置空集合
        /*List<BigDecimal> reservePurchaseQty = new ArrayList<>();
        for (Date ignored : dates) {
            reservePurchaseQty.add(BigDecimal.ZERO);
        }*/
        //获取实际销量
        //获取通用其它指标传输实体对象
        OtherTargetGeneralQueryDTO otherQueryDTO = OtherTargetHandleUtil.getGeneralQueryDTO(esAggregationDTO, result, OtherTargetsEnum.SALES_PALN);
        OtherTargetHandleUtil.handleResult(otherTargetGeneralHandlerFacade.getNecessaryOtherTargets(otherQueryDTO), result);
        for (EsReturnDTO esReturnDTO : result) {
            this.setSalesPlanInfoDTOCommon(esReturnDTO, pageList);
        }
        planResponse.setInfoList(pageList);
        planResponse.setDateList(MyDateUtil.processCyclesDate(weekDates, "M.dd"));
        return planResponse;
    }

    /**
     * 校验登录用户渠道权限，并返回用户拥有的渠道权限
     *
     * @param channelCodeList 渠道编码集合
     * @return List<String>
     * @author wuyuanhang
     * @date 2020/11/10
     */
    private List<String> checkChannelPermissions(List<String> channelCodeList) {
        // 获取登录用户的渠道权限集合
        List<String> channelPermissionList = permissionService.getPermissionChannelList();
//        List<String> channelPermissionList = Arrays.asList("C00001");
        if (CollectionUtils.isEmpty(channelPermissionList)) {
            throw new RomeException(ResponseMsg.FAIL_6009.getCode(), ResponseMsg.FAIL_6009.getMsg());
        }
        if (!CollectionUtils.isEmpty(channelCodeList)) {
            // 取交集
            channelPermissionList.retainAll(channelCodeList);
        }
        return channelPermissionList;
    }

    /**
     * 设置指标名称
     *
     * @return
     */
    private SalesPlanInfoDTO getSalesPlanInfoDTO() {
        SalesPlanInfoDTO salesPlanInfoDTO = new SalesPlanInfoDTO();
        salesPlanInfoDTO.setForecastPlanQtyLabel(DemandPlanTargetEnum.FORECAST_PLAN_QTY.getDesc());
        salesPlanInfoDTO.setSalesPlanQtyLabel(DemandPlanTargetEnum.SALES_PLAN_QTY.getDesc());
        salesPlanInfoDTO.setLastSalesPlanQtyLabel(DemandPlanTargetEnum.LAST_SALES_PLAN_QTY.getDesc());
        salesPlanInfoDTO.setConfirmQtyLabel(DemandPlanTargetEnum.CONFIRM_QTY.getDesc());
        salesPlanInfoDTO.setLastConfirmQtyLabel(DemandPlanTargetEnum.LAST_CONFIRM_QTY.getDesc());
        salesPlanInfoDTO.setPlanDifferenceLabel(DemandPlanTargetEnum.PLAN_CONFIRMED_DIFFERIN.getDesc());
        salesPlanInfoDTO.setPlanProportionLabel(DemandPlanTargetEnum.PLAN_CONFIRMED_PROPORTION.getDesc());
        salesPlanInfoDTO.setActualSalesQtyLabel(DemandPlanTargetEnum.ACTUAL_SALES_QTY.getDesc());
/*        salesPlanInfoDTO.setConfirmTarget2Label(SalesPlanDirectlyEnum.CONFIRM_TARGET2.getDesc());
        salesPlanInfoDTO.setConfirmTarget3Label(SalesPlanDirectlyEnum.CONFIRM_TARGET3.getDesc());
        salesPlanInfoDTO.setConfirmTarget4Label(SalesPlanDirectlyEnum.CONFIRM_TARGET4.getDesc());*/
        return salesPlanInfoDTO;
    }

    private EsAggregationDTO setSearchFields(SalesPlanDTO salesPlanDTO, List<SalesPlanSelectDTO> categoryCodeList, List<SalesPlanSelectDTO> areaSalesPlanSelectDTOs) {
        EsAggregationDTO esAggregationDTO = new EsAggregationDTO();
        EsQueryDTO esQueryDTO = new EsQueryDTO();
        List<String> groupColumns = new ArrayList<>();
        //渠道
        if (!CollectionUtils.isEmpty(salesPlanDTO.getChannelCodeList())) {
            esQueryDTO.setChannelCodeList(salesPlanDTO.getChannelCodeList());
        }
        //计划类型
        if (!CollectionUtils.isEmpty(salesPlanDTO.getPlanTypeList())) {
            esQueryDTO.setTypeList(salesPlanDTO.getPlanTypeList());
        }
        //品类查询条件设置
        if (!CollectionUtils.isEmpty(categoryCodeList)) {
            esQueryDTO.setCategoryCodeList(categoryCodeList);
        }
        //地域查询条件设置
        this.setPermissionAreaList(esQueryDTO);
        if (!CollectionUtils.isEmpty(areaSalesPlanSelectDTOs)) {
            esQueryDTO.setQueryAreaCodeList(salesPlanDTO.getAreaCodeList());
            Integer level = areaSalesPlanSelectDTOs.get(0).getLevel();
            if (CommonConstants.AREA_LEVEL_ONE.equals(level)) {
                groupColumns.add(EsConmulEnum.PROVINCE_CODE_KEYWORD.getFName());
                salesPlanDTO.setExistPro(true);
            }
            if (CommonConstants.AREA_LEVEL_TWO.equals(level)) {
                groupColumns.add(EsConmulEnum.CITY_CODE_KEYWORD.getFName());
                salesPlanDTO.setExistCity(true);
            }
            if (CommonConstants.AREA_LEVEL_THREE.equals(level)) {
                groupColumns.add(EsConmulEnum.COUNTY_CODE_KEYWORD.getFName());
                salesPlanDTO.setExistCount(true);
            }
            if (CommonConstants.AREA_LEVEL_FOUR.equals(level)) {
                groupColumns.add(EsConmulEnum.STORE_KEYWORD.getFName());
                salesPlanDTO.setExistStore(true);
            }
        } else {
            //默认按照省份分组
            groupColumns.add(EsConmulEnum.PROVINCE_CODE_KEYWORD.getFName());
            salesPlanDTO.setExistPro(true);
        }
        // 商品单位
        if (!Objects.isNull(salesPlanDTO.getUnitType())) {
            esAggregationDTO.setUnitType(salesPlanDTO.getUnitType());
        }
        groupColumns.add(EsConmulEnum.CHANNEL_KEYWORD.getFName());
        groupColumns.add(EsConmulEnum.PLAN_TYPE.getFName());
        groupColumns.add(EsConmulEnum.SKU_KEYWORD.getFName());
        List<String> sumColumns = this.setSumColumns();
        esAggregationDTO.setGroupColumns(groupColumns);
        esAggregationDTO.setSumColumns(sumColumns);
        esAggregationDTO.setEsQuery(esQueryDTO);
        return esAggregationDTO;
    }

    private void setPermissionAreaList(EsQueryDTO esQueryDTO) {
        // 获取登录用户的区域权限集合，包括门店
        List<SalesPlanSelectDTO> areaPermissionList = permissionService.getPermissionAreaList();
//        List<SalesPlanSelectDTO> areaPermissionList = new ArrayList<>();
//        SalesPlanSelectDTO area1 = new SalesPlanSelectDTO();
//        area1.setCode("310000");
//        area1.setLevel(1);
//        area1.setName("上海");
//        area1.setParentCode("100000");
//        SalesPlanSelectDTO area2 = new SalesPlanSelectDTO();
//        area2.setCode("320000");
//        area2.setLevel(1);
//        area2.setName("江苏省");
//        area2.setParentCode("100000");
//        SalesPlanSelectDTO area3 = new SalesPlanSelectDTO();
//        area3.setCode("330000");
//        area3.setLevel(1);
//        area3.setName("浙江省");
//        area3.setParentCode("100000");
//        areaPermissionList.add(area1);
//        areaPermissionList.add(area2);
//        areaPermissionList.add(area3);
        if (CollectionUtils.isEmpty(areaPermissionList)) {
            throw new RomeException(ResponseMsg.FAIL_6005.getCode(), ResponseMsg.FAIL_6005.getMsg());
        }
        esQueryDTO.setPermissionAreaList(areaPermissionList);
    }

    /**
     * 查询出需求计划柱状图的指标数据
     *
     * @param salesPlanDTO 需求计划
     * @return {@link EChartsDTO}
     */
    @Override
    public EChartsDTO listAllByCycles(SalesPlanDTO salesPlanDTO) {
        // 校验登录用户渠道权限
        List<String> channelCodeList = this.checkChannelPermissions(salesPlanDTO.getChannelCodeList());
        salesPlanDTO.setChannelCodeList(channelCodeList);
        List<SalesPlanSelectDTO> categorySalesPlanSelectDTOs = itemCoreFacade.getSubcategoryCode(salesPlanDTO.getCategoryCodeList());
        List<SalesPlanSelectDTO> areaSalesPlanSelectDTOs = baseDataFacade.getSubAreaCode(salesPlanDTO.getAreaCodeList());
        Date startTime = salesPlanDTO.getStartTime();
        Date endTime = salesPlanDTO.getEndTime();
        salesPlanDTO.setStartTime(DateUtil.beginOfDay(DateUtil.beginOfWeek(startTime)));
        List<Map<String, Date>> weekDates = null;
        //按周
        if (null != endTime) {
            //结束时间设置为周日
            weekDates = MyDateUtil.getWeekDates(startTime, endTime);
        }
        if (null == endTime) {
            //默认14周
            weekDates = MyDateUtil.getWeekDates(startTime, DataDisplayCycleEnum.WEEKLY_FOURTEEN.getValue());
        }
        EsAggregationDTO esAggregationDTO = this.setEchartSearchFields(salesPlanDTO, categorySalesPlanSelectDTOs, areaSalesPlanSelectDTOs);
        esAggregationDTO.getEsQuery().setStartDate(DateUtil.format(weekDates.get(0).get("startTime"), DatePattern.NORM_DATE_PATTERN));
        esAggregationDTO.getEsQuery().setEndDate(DateUtil.format(weekDates.get(weekDates.size() - 1).get("endTime"), DatePattern.NORM_DATE_PATTERN));
        //获取起始十四周起始日期字符串集合
        List<String> dateList = new ArrayList<>();
        List<Date> dates = new ArrayList<>();
        for (Map<String, Date> item : weekDates) {
            dateList.add(DateUtil.format(item.get("startTime"), DatePattern.NORM_DATE_PATTERN));
            dates.add(item.get("startTime"));
        }
        esAggregationDTO.setDateList(dateList);
        //调用service从查询出对应的指标
        PageInfo<EsReturnDTO> esReturnDTOS = salesPlanEsFacade.queryByGroup(esAggregationDTO);
        if (Objects.isNull(esReturnDTOS) || CollectionUtils.isEmpty(esReturnDTOS.getList())) {
            return null;
        }
        EChartsDTO eChartsDTO = new EChartsDTO();
        List<EsReturnDTO> esReturnDTOSList = esReturnDTOS.getList();
        //返回对应的key和label值
        List<EChartsLabelDTO> labelData = new ArrayList<>();
        DemandPlanTargetEnum[] values = DemandPlanTargetEnum.getNeedExportTargets();
        for (DemandPlanTargetEnum value : values) {
            EChartsLabelDTO eChartsLabelDTO = new EChartsLabelDTO();
            eChartsLabelDTO.setKey(value.getColumns());
            eChartsLabelDTO.setLabel(value.getDesc());
            labelData.add(eChartsLabelDTO);
        }
        //TODO 临时将实际销量指标设置空集合
        /*List<BigDecimal> reservePurchaseQty = new ArrayList<>();
        for (Date ignored : dates) {
            reservePurchaseQty.add(BigDecimal.ZERO);
        }*/
        //获取实际销量
        //获取通用其它指标传输实体对象
        OtherTargetGeneralQueryDTO otherQueryDTO = OtherTargetHandleUtil.getGeneralEchartQueryDTO(esAggregationDTO, OtherTargetsEnum.SALES_PALN, null);
        otherQueryDTO.setType(CommonConstants.ONE);
        OtherTargetHandleUtil.handleEchartResult(otherTargetGeneralHandlerFacade.getEchartNecessaryOtherTargets(otherQueryDTO), esReturnDTOSList.get(0).getTargetMap());
        Map<String, List<BigDecimal>> value = new HashMap<>();
        Map<String, List<BigDecimal>> targetMap;
        for (EsReturnDTO esReturnDTO : esReturnDTOSList) {
            targetMap = esReturnDTO.getTargetMap();
            value.put(DemandPlanTargetEnum.SALES_PLAN_QTY.getColumns(), targetMap.get(EsSumColumnEnum.SALES_PLAN_QTY.getColumn()));
            value.put(DemandPlanTargetEnum.LAST_SALES_PLAN_QTY.getColumns(), targetMap.get(EsSumColumnEnum.LAST_PREDICT_SALES.getColumn()));
            value.put(DemandPlanTargetEnum.FORECAST_PLAN_QTY.getColumns(), targetMap.get(EsSumColumnEnum.FORECAST_PLAN_QTY.getColumn()));
            value.put(DemandPlanTargetEnum.CONFIRM_QTY.getColumns(), targetMap.get(EsSumColumnEnum.CONFIRM_L4.getColumn()));
            value.put(DemandPlanTargetEnum.LAST_CONFIRM_QTY.getColumns(), targetMap.get(EsSumColumnEnum.LAST_CONFIRM_L4.getColumn()));
            value.put(DemandPlanTargetEnum.PLAN_CONFIRMED_DIFFERIN.getColumns(), targetMap.get(EsSumColumnEnum.PREDICT_CONFIRM_L4_DIFFERENCE.getColumn()));
            value.put(DemandPlanTargetEnum.PLAN_CONFIRMED_PROPORTION.getColumns(), targetMap.get(EsSumColumnEnum.PREDICT_CONFIRM_L4_SCALE.getColumn()));
            value.put(DemandPlanTargetEnum.ACTUAL_SALES_QTY.getColumns(), targetMap.get(OtherTargetsEnum.ACTUAL_SALES.getName()));
/*            value.put(DemandPlanTargetEnum.CONFIRM_TARGET2.getColumns(), targetMap.get(EsSumColumnEnum.CONFIRM_TARGET_L2.getColumn()));
            value.put(DemandPlanTargetEnum.CONFIRM_TARGET3.getColumns(), targetMap.get(EsSumColumnEnum.CONFIRM_TARGET_L3.getColumn()));
            value.put(DemandPlanTargetEnum.CONFIRM_TARGET4.getColumns(), targetMap.get(EsSumColumnEnum.CONFIRM_TARGET_L4.getColumn()));*/
        }
        eChartsDTO.setLabelData(labelData);
        eChartsDTO.setDateList(MyDateUtil.processCyclesDate(weekDates, "M.dd"));
        eChartsDTO.setValue(value);
        return eChartsDTO;
    }

    private EsAggregationDTO setEchartSearchFields(SalesPlanDTO salesPlanDTO, List<SalesPlanSelectDTO> categoryCodeList, List<SalesPlanSelectDTO> areaSalesPlanSelectDTOs) {
        EsAggregationDTO esAggregationDTO = new EsAggregationDTO();
        EsQueryDTO esQueryDTO = new EsQueryDTO();
        List<String> groupColumns = new ArrayList<>();
        //渠道
        if (!CollectionUtils.isEmpty(salesPlanDTO.getChannelCodeList())) {
            esQueryDTO.setChannelCodeList(salesPlanDTO.getChannelCodeList());
        }
        //计划类型
        if (!CollectionUtils.isEmpty(salesPlanDTO.getPlanTypeList())) {
            esQueryDTO.setTypeList(salesPlanDTO.getPlanTypeList());
        }
        //品类查询条件设置
        if (!CollectionUtils.isEmpty(categoryCodeList)) {
            esQueryDTO.setCategoryCodeList(categoryCodeList);
        }
        //地域查询条件设置
        this.setPermissionAreaList(esQueryDTO);
        if (!CollectionUtils.isEmpty(areaSalesPlanSelectDTOs)) {
            esQueryDTO.setQueryAreaCodeList(salesPlanDTO.getAreaCodeList());
        }
        // 商品单位
        if (!Objects.isNull(salesPlanDTO.getUnitType())) {
            esAggregationDTO.setUnitType(salesPlanDTO.getUnitType());
        }
        List<String> sumColumns = this.setSumColumns();
        esAggregationDTO.setGroupColumns(groupColumns);
        esAggregationDTO.setSumColumns(sumColumns);
        esAggregationDTO.setEsQuery(esQueryDTO);
        return esAggregationDTO;
    }

    /**
     * 设置聚合列集合
     *
     * @return List<String>
     * @author wuyuanhang
     * @date 2020/11/11
     */
    private List<String> setSumColumns() {
        List<String> sumColumns = new ArrayList<>();
        sumColumns.add(EsSumColumnEnum.SALES_PLAN_QTY.getColumn());
        sumColumns.add(EsSumColumnEnum.LAST_PREDICT_SALES.getColumn());
        sumColumns.add(EsSumColumnEnum.CONFIRM_L4.getColumn());
        sumColumns.add(EsSumColumnEnum.LAST_CONFIRM_L4.getColumn());
        sumColumns.add(EsSumColumnEnum.PREDICT_CONFIRM_L4_DIFFERENCE.getColumn());
        sumColumns.add(EsSumColumnEnum.PREDICT_CONFIRM_L4_SCALE.getColumn());
        sumColumns.add(EsSumColumnEnum.FORECAST_PLAN_QTY.getColumn());

/*        sumColumns.add(EsSumColumnEnum.CONFIRM_TARGET_L2.getColumn());
        sumColumns.add(EsSumColumnEnum.CONFIRM_TARGET_L3.getColumn());
        sumColumns.add(EsSumColumnEnum.CONFIRM_TARGET_L4.getColumn());*/
        return sumColumns;
    }

    /**
     * 导出Excel
     *
     * @param excelExportDTO excel导出dto
     * @param type           导出类型-1导出当前页-2导出选中数据-3导出所有数据
     * @return ExcelExportDataDTO
     * @author wuyuanhang
     * @date 2020/11/09
     */
    private ExcelExportDataDTO exportExcel(ExcelExportDTO excelExportDTO, Integer type, List<String> channelPermissionList, List<SalesPlanSelectDTO> areaPermissionList, Long userId) {
        if (Objects.isNull(type)) {
            throw new RomeException(ResponseMsg.FAIL_2005.getCode(), ResponseMsg.FAIL_2005.getMsg());
        }
        // 校验登录用户渠道权限
        if (CollectionUtils.isEmpty(channelPermissionList)) {
            throw new RomeException(ResponseMsg.FAIL_6009.getCode(), ResponseMsg.FAIL_6009.getMsg());
        }
        if (!CollectionUtils.isEmpty(excelExportDTO.getChannelCodeList())) {
            // 取交集
            channelPermissionList.retainAll(excelExportDTO.getChannelCodeList());
        }
        excelExportDTO.setChannelCodeList(channelPermissionList);
        List<SalesPlanSelectDTO> areaSalesPlanSelectDTOs = baseDataFacade.getSubAreaCode(excelExportDTO.getAreaCodeList());
        EsExportAggregationDTO esAggregationDTO = this.setExportSearchFields(excelExportDTO, excelExportDTO.getCategoryCodeList(), areaSalesPlanSelectDTOs, areaPermissionList);
        List<Map<String, Date>> weekDates = MyDateUtil.getWeekDates(excelExportDTO.getStartTime(), DataDisplayCycleEnum.WEEKLY_FOURTEEN.getValue());
        esAggregationDTO.getEsQuery().setStartDate(DateUtil.format(weekDates.get(0).get("startTime"), DatePattern.NORM_DATE_PATTERN));
        esAggregationDTO.getEsQuery().setEndDate(DateUtil.format(weekDates.get(weekDates.size() - 1).get("endTime"), DatePattern.NORM_DATE_PATTERN));
        //获取起始十四周起始日期字符串集合
        List<String> dateList = new ArrayList<>();
        List<Date> dates = new ArrayList<>();
        for (Map<String, Date> item : weekDates) {
            dateList.add(DateUtil.format(item.get("startTime"), DatePattern.NORM_DATE_PATTERN));
            dates.add(item.get("startTime"));
        }
        esAggregationDTO.setDateList(dateList);
        EsAggregationDTO esDto = new EsAggregationDTO();
        esDto.setDateList(dateList);
        List<String> groupColumns = new ArrayList<>();
        if (!CollectionUtils.isEmpty(areaSalesPlanSelectDTOs)) {
            Integer level = areaSalesPlanSelectDTOs.get(0).getLevel();
            if (CommonConstants.AREA_LEVEL_ONE.equals(level)) {
                groupColumns.add(EsConmulEnum.PROVINCE_CODE_KEYWORD.getFName());
            }
            if (CommonConstants.AREA_LEVEL_TWO.equals(level)) {
                groupColumns.add(EsConmulEnum.CITY_CODE_KEYWORD.getFName());
            }
            if (CommonConstants.AREA_LEVEL_THREE.equals(level)) {
                groupColumns.add(EsConmulEnum.COUNTY_CODE_KEYWORD.getFName());
            }
            if (CommonConstants.AREA_LEVEL_FOUR.equals(level)) {
                groupColumns.add(EsConmulEnum.STORE_KEYWORD.getFName());
            }
        } else {
            //默认按照省份分组
            groupColumns.add(EsConmulEnum.PROVINCE_CODE_KEYWORD.getFName());
        }
        groupColumns.add(EsConmulEnum.CHANNEL_KEYWORD.getFName());
        groupColumns.add(EsConmulEnum.PLAN_TYPE.getFName());
        groupColumns.add(EsConmulEnum.SKU_KEYWORD.getFName());
        esDto.setGroupColumns(groupColumns);
        List<List<Object>> exportDataList = new ArrayList<>();
        List<String> allTargetsDesc = DemandPlanTargetEnum.getNeedExportTargetsDesc();
        if (!ExcelExportTypeEnum.SEARCHED_DATA_EXPORT.getType().equals(type)) {
            esAggregationDTO.setPageSize(excelExportDTO.getPageSize());
            esAggregationDTO.setPageNum(excelExportDTO.getPageNum());
            //从缓存中拿导出数据
            List<EsReturnDTO> result = salesPlanEsLazyLoadingFacade.queryFromCache(this.getTransfer(esAggregationDTO, userId));
            if (CollectionUtils.isEmpty(result)) {
                throw new RomeException(ResponseMsg.FAIL_2005.getCode(), ResponseMsg.FAIL_2005.getMsg());
            }
            OtherTargetHandleUtil.handleResult(otherTargetGeneralHandlerFacade.getNecessaryOtherTargets(OtherTargetHandleUtil.getGeneralQueryDTO(this.getTransfer(esAggregationDTO, userId), result, OtherTargetsEnum.SALES_PALN)), result);
            List<SalesPlanInfoDTO> pageList = this.getExportPageList(esDto, dates, result, excelExportDTO.getRowIndex());
            this.getExportDataList(exportDataList, pageList, allTargetsDesc);
        }
        if (ExcelExportTypeEnum.SEARCHED_DATA_EXPORT.getType().equals(type)) {
            EsScrollReturnDTO esScrollReturnDTO;
            do {
                esScrollReturnDTO = salesPlanEsFacade.exportByGroup(esAggregationDTO);
                if (!CollectionUtils.isEmpty(esScrollReturnDTO.getEsReturnDTOList())) {
                    OtherTargetHandleUtil.handleResult(otherTargetGeneralHandlerFacade.getNecessaryOtherTargets(OtherTargetHandleUtil.getGeneralQueryDTO(this.getTransfer(esAggregationDTO, userId), esScrollReturnDTO.getEsReturnDTOList(), OtherTargetsEnum.SALES_PALN)), esScrollReturnDTO.getEsReturnDTOList());
                    this.getExportDataList(exportDataList, this.getExportList(esDto, dates, esScrollReturnDTO.getEsReturnDTOList(), weekDates), allTargetsDesc);
                }
                esAggregationDTO.setAfterKey(esScrollReturnDTO.getAfterKey());
            } while (esScrollReturnDTO.getAfterKey() != null);
        }

        // 获取指标名称集合
        List<String> targetsDesc = DemandPlanTargetEnum.getNeedExportTargetsDesc();

        // 设置Excel表头，并返回需要合并单元格的列数
        List<List<String>> titles = new ArrayList<>();
        //获取excel标题信息
        List<String> dateTitles = MyDateUtil.processCyclesDate(weekDates, "MM.dd");
        int mergeCellsColumns = this.setExcelTitles(titles, dateTitles);
        return ExcelExportUtil.getExcelExportData(mergeCellsColumns, exportDataList, titles, targetsDesc.size(), new SalesPlanCellWriterHandler());
    }

    /**
     * 设置Excel表头，并返回需要合并单元格的列数
     *
     * @param titles     Title集合
     * @param dateTitles 日期列集合
     * @return int
     */
    private int setExcelTitles(List<List<String>> titles, List<String> dateTitles) {
        // 写死需要合并单元格的列：渠道、计划类型、区域、品类、商品
        AdvancedSearchUtil.setList(titles, ExcelExportTitleEnum.CHANNEL_TITLE.getTitle());
        AdvancedSearchUtil.setList(titles, ExcelExportTitleEnum.PLAN_TITLE.getTitle());
        AdvancedSearchUtil.setList(titles, ExcelExportTitleEnum.AREA_TITLE.getTitle());
        AdvancedSearchUtil.setList(titles, ExcelExportTitleEnum.CATEGORY_TITLE.getTitle());
        AdvancedSearchUtil.setList(titles, ExcelExportTitleEnum.SKU_TITLE.getTitle());
        int mergeCellsColumns = titles.size();
        //加入指标标题
        List<String> targetTitle = new ArrayList<>();
        targetTitle.add(ExcelExportTitleEnum.TARGET_TITLE.getTitle());
        titles.add(targetTitle);
        //加入日期标题
        AdvancedSearchUtil.getExcelDateTitle(titles, dateTitles);
        return mergeCellsColumns;
    }

    private EsExportAggregationDTO setExportSearchFields(ExcelExportDTO excelExportDTO, List<SalesPlanSelectDTO> categoryCodeList, List<SalesPlanSelectDTO> areaSalesPlanSelectDTOs, List<SalesPlanSelectDTO> areaPermissionList) {
        EsExportAggregationDTO esExportAggregationDTO = new EsExportAggregationDTO();
        EsQueryDTO esQueryDTO = new EsQueryDTO();
        List<String> groupColumns = new ArrayList<>();
        //渠道
        if (!CollectionUtils.isEmpty(excelExportDTO.getChannelCodeList())) {
            esQueryDTO.setChannelCodeList(excelExportDTO.getChannelCodeList());
        }
        //计划类型
        if (!CollectionUtils.isEmpty(excelExportDTO.getTypeList())) {
            esQueryDTO.setTypeList(excelExportDTO.getTypeList());
        }
        //品类查询条件设置
        if (!CollectionUtils.isEmpty(categoryCodeList)) {
            esQueryDTO.setCategoryCodeList(categoryCodeList);
        }
        //地域查询条件设置
        // 获取登录用户的区域权限集合，包括门店
        if (CollectionUtils.isEmpty(areaPermissionList)) {
            throw new RomeException(ResponseMsg.FAIL_6005.getCode(), ResponseMsg.FAIL_6005.getMsg());
        }
        esQueryDTO.setPermissionAreaList(areaPermissionList);
        if (!CollectionUtils.isEmpty(areaSalesPlanSelectDTOs)) {
            esQueryDTO.setQueryAreaCodeList(excelExportDTO.getAreaCodeList());
            Integer level = areaSalesPlanSelectDTOs.get(0).getLevel();
            if (CommonConstants.AREA_LEVEL_ONE.equals(level)) {
                groupColumns.add(EsConmulEnum.PROVINCE_CODE_KEYWORD.getFName());
            }
            if (CommonConstants.AREA_LEVEL_TWO.equals(level)) {
                groupColumns.add(EsConmulEnum.CITY_CODE_KEYWORD.getFName());
            }
            if (CommonConstants.AREA_LEVEL_THREE.equals(level)) {
                groupColumns.add(EsConmulEnum.COUNTY_CODE_KEYWORD.getFName());
            }
            if (CommonConstants.AREA_LEVEL_FOUR.equals(level)) {
                groupColumns.add(EsConmulEnum.STORE_KEYWORD.getFName());
            }
        } else {
            //默认按照省份分组
            groupColumns.add(EsConmulEnum.PROVINCE_CODE_KEYWORD.getFName());
        }
        // 商品单位
        if (!Objects.isNull(excelExportDTO.getUnitType())) {
            esExportAggregationDTO.setUnitType(excelExportDTO.getUnitType());
        }
        groupColumns.add(EsConmulEnum.CHANNEL_KEYWORD.getFName());
        groupColumns.add(EsConmulEnum.PLAN_TYPE.getFName());
        groupColumns.add(EsConmulEnum.SKU_KEYWORD.getFName());
        List<String> sumColumns = this.setSumColumns();
        esExportAggregationDTO.setGroupColumns(groupColumns);
        esExportAggregationDTO.setSumColumns(sumColumns);
        esExportAggregationDTO.setEsQuery(esQueryDTO);
        esExportAggregationDTO.setAfterKey("");
        return esExportAggregationDTO;
    }

    private EsAggregationDTO getTransfer(EsExportAggregationDTO esAggregationDTO, Long userId) {
        EsAggregationDTO esAggregation = new EsAggregationDTO();
        esAggregation.setDateList(esAggregationDTO.getDateList());
        esAggregation.setEsQuery(esAggregationDTO.getEsQuery());
        esAggregation.setSumColumns(esAggregationDTO.getSumColumns());
        esAggregation.setGroupColumns(esAggregationDTO.getGroupColumns());
        esAggregation.setPageNum(esAggregationDTO.getPageNum());
        esAggregation.setPageSize(esAggregationDTO.getPageSize());
        esAggregation.setTableCode(TableCodeEnum.SALE_PLAN.getCode());
        esAggregation.setUnitType(esAggregationDTO.getUnitType());
        esAggregation.setUserId(userId);
        return esAggregation;
    }

    private List<SalesPlanInfoDTO> getExportPageList(EsAggregationDTO esAggregationDTO, List<Date> dates, List<EsReturnDTO> result, List<Integer> indexs) {
        boolean flag = false;
        if (indexs != null && indexs.size() > 0) {
            flag = true;
        }
        List<SalesPlanInfoDTO> pageList = new ArrayList<>();
        //TODO 临时将实际销量指标设置空集合
        /*List<BigDecimal> reservePurchaseQty = new ArrayList<>();
        for (Date ignored : dates) {
            reservePurchaseQty.add(BigDecimal.ZERO);
        }*/
        //获取实际销量
        //获取通用其它指标传输实体对象
        int index = 0;
        for (EsReturnDTO esReturnDTO : result) {
            if (flag && !indexs.contains(index)) {
                index++;
                continue;
            }
            index++;
            this.setSalesPlanInfoDTOCommon(esReturnDTO, pageList);
        }
        return pageList;
    }

    private void getExportDataList(List<List<Object>> exportDataList, List<SalesPlanInfoDTO> pageList, List<String> allTargetsDesc) {
        List<Object> itemRow;
        for (SalesPlanInfoDTO item : pageList) {
            for (String target : allTargetsDesc) {
                itemRow = new ArrayList<>();
                itemRow.add(item.getChannel());
                itemRow.add(item.getPlanTypeName());
                itemRow.add(item.getArea());
                itemRow.add(item.getCategory());
                itemRow.add(item.getSkuName() + "/" + item.getSkuCode());
                itemRow.add(target);
                //加入数据
                for (int j = 0; j < DataDisplayCycleEnum.WEEKLY_FOURTEEN.getValue(); j++) {
                    if (DemandPlanTargetEnum.SALES_PLAN_QTY.getDesc().equals(target)) {
                        itemRow.add(item.getSalesPlanQty().get(j).compareTo(BigDecimal.ZERO) == 0 ? "-" : item.getSalesPlanQty().get(j));
                    } else if (DemandPlanTargetEnum.LAST_SALES_PLAN_QTY.getDesc().equals(target)) {
                        itemRow.add(item.getLastSalesPlanQty().get(j).compareTo(BigDecimal.ZERO) == 0 ? "-" : item.getLastSalesPlanQty().get(j));
                    } else if (DemandPlanTargetEnum.CONFIRM_QTY.getDesc().equals(target)) {
                        itemRow.add(item.getConfirmQty().get(j).compareTo(BigDecimal.ZERO) == 0 ? "-" : item.getConfirmQty().get(j));
                    } else if (DemandPlanTargetEnum.LAST_CONFIRM_QTY.getDesc().equals(target)) {
                        itemRow.add(item.getLastConfirmQty().get(j).compareTo(BigDecimal.ZERO) == 0 ? "-" : item.getLastConfirmQty().get(j));
                    } else if (DemandPlanTargetEnum.PLAN_CONFIRMED_DIFFERIN.getDesc().equals(target)) {
                        itemRow.add(item.getPlanDifferenceQty().get(j).compareTo(BigDecimal.ZERO) == 0 ? "-" : item.getPlanDifferenceQty().get(j));
                    } else if (DemandPlanTargetEnum.PLAN_CONFIRMED_PROPORTION.getDesc().equals(target)) {
                        itemRow.add(item.getPlanProportionQty().get(j).compareTo(BigDecimal.ZERO) == 0 ? "-" : item.getPlanProportionQty().get(j));
                    } else if (DemandPlanTargetEnum.FORECAST_PLAN_QTY.getDesc().equals(target)) {
                        itemRow.add(item.getForecastPlanQty().get(j).compareTo(BigDecimal.ZERO) == 0 ? "-" : item.getForecastPlanQty().get(j));
                    } else if (DemandPlanTargetEnum.ACTUAL_SALES_QTY.getDesc().equals(target)) {
                        itemRow.add(item.getActualSalesQty().get(j).compareTo(BigDecimal.ZERO) == 0 ? "-" : item.getActualSalesQty().get(j));
                    }

/*                    else if(DemandPlanTargetEnum.CONFIRM_TARGET2.getDesc().equals(target)){
                        itemRow.add(item.getConfirmTarget2Qty().get(j).compareTo(BigDecimal.ZERO) == 0 ? "-" : item.getConfirmTarget2Qty().get(j));
                    }else if(DemandPlanTargetEnum.CONFIRM_TARGET3.getDesc().equals(target)){
                        itemRow.add(item.getConfirmTarget3Qty().get(j).compareTo(BigDecimal.ZERO) == 0 ? "-" : item.getConfirmTarget3Qty().get(j));
                    }else if(DemandPlanTargetEnum.CONFIRM_TARGET4.getDesc().equals(target)){
                        itemRow.add(item.getConfirmTarget4Qty().get(j).compareTo(BigDecimal.ZERO) == 0 ? "-" : item.getConfirmTarget4Qty().get(j));
                    }*/
                }
                exportDataList.add(itemRow);
            }
        }
    }

    private List<SalesPlanInfoDTO> getExportList(EsAggregationDTO esAggregationDTO, List<Date> dates, List<EsReturnDTO> result, List<Map<String, Date>> weekDates) {
        List<SalesPlanInfoDTO> pageList = new ArrayList<>();
        //TODO 临时将实际销量指标设置空集合
        /*List<BigDecimal> reservePurchaseQty = new ArrayList<>();
        for (Date ignored : dates) {
            reservePurchaseQty.add(BigDecimal.ZERO);
        }*/
        //获取实际销量
        //获取通用其它指标传输实体对象
        for (EsReturnDTO esReturnDTO : result) {
            this.setSalesPlanInfoDTOCommon(esReturnDTO, pageList);
        }
        return pageList;
    }

    /**
     * 公共代码
     *
     * @param esReturnDTO
     * @param pageList
     * @author wuyuanhang
     * @date 2020/11/12
     */
    private void setSalesPlanInfoDTOCommon(EsReturnDTO esReturnDTO, List<SalesPlanInfoDTO> pageList) {
        SalesPlanInfoDTO salesPlanInfoDTO = this.getSalesPlanInfoDTO();
        salesPlanInfoDTO.setChannel(esReturnDTO.getChannelName());
        salesPlanInfoDTO.setArea(esReturnDTO.getArea());
        salesPlanInfoDTO.setPlanTypeName(esReturnDTO.getPlanTypeName());
        salesPlanInfoDTO.setCategory(esReturnDTO.getCategory());
        salesPlanInfoDTO.setSkuName(esReturnDTO.getSkuName());
        salesPlanInfoDTO.setSkuCode(esReturnDTO.getSkuCode());
        //指标
        Map<String, List<BigDecimal>> targetmap = esReturnDTO.getTargetMap();
        salesPlanInfoDTO.setForecastPlanQty(targetmap.get(EsSumColumnEnum.FORECAST_PLAN_QTY.getColumn()));
        salesPlanInfoDTO.setSalesPlanQty(targetmap.get(EsSumColumnEnum.SALES_PLAN_QTY.getColumn()));
        salesPlanInfoDTO.setLastSalesPlanQty(targetmap.get(EsSumColumnEnum.LAST_PREDICT_SALES.getColumn()));
        salesPlanInfoDTO.setConfirmQty(targetmap.get(EsSumColumnEnum.CONFIRM_L4.getColumn()));
        salesPlanInfoDTO.setLastConfirmQty(targetmap.get(EsSumColumnEnum.LAST_CONFIRM_L4.getColumn()));
        salesPlanInfoDTO.setPlanDifferenceQty(targetmap.get(EsSumColumnEnum.PREDICT_CONFIRM_L4_DIFFERENCE.getColumn()));
        salesPlanInfoDTO.setPlanProportionQty(targetmap.get(EsSumColumnEnum.PREDICT_CONFIRM_L4_SCALE.getColumn()));
        salesPlanInfoDTO.setActualSalesQty(targetmap.get(OtherTargetsEnum.ACTUAL_SALES.getName()));
/*        salesPlanInfoDTO.setConfirmTarget2Qty(targetmap.get(EsSumColumnEnum.CONFIRM_TARGET_L2.getColumn()));
        salesPlanInfoDTO.setConfirmTarget3Qty(targetmap.get(EsSumColumnEnum.CONFIRM_TARGET_L3.getColumn()));
        salesPlanInfoDTO.setConfirmTarget4Qty(targetmap.get(EsSumColumnEnum.CONFIRM_TARGET_L4.getColumn()));*/
        pageList.add(salesPlanInfoDTO);
    }

    // TODO: 2020/11/12 等入参及返回结果的数据结构稳定后再对接
    private ActualSalesQtyQueryDTO setGroupByChannelAreaSkuParam(List<SalesPlanInfoDTO> pageList, List<SalesPlanSelectDTO> areaSalesPlanSelectDTOs, Date startTime, Date endTime, List<String> dateList) {
        ActualSalesQtyQueryDTO param = new ActualSalesQtyQueryDTO();
        List<String> kpChannelList = new ArrayList<>();
        List<String> skuList = new ArrayList<>();
        List<ActualSalesQtyQueryDTO.AreaCondition> areaConditionList = new ArrayList<>();
        for (SalesPlanInfoDTO data : pageList) {
            if (!kpChannelList.contains(data.getChannelCode())) {
                kpChannelList.add(data.getChannelCode());
            }
            if (!skuList.contains(data.getSkuCode())) {
                skuList.add(data.getSkuCode());
            }
        }
        param.setKpChannelList(kpChannelList);
/*        ActualSalesQtyQueryDTO.AreaCondition areaCondition = new ActualSalesQtyQueryDTO.AreaCondition();
        param.setCategoryCondition(salesPlanDTO.getCategoryCodeList());
        param.setAreaCondition(salesPlanDTO.getAreaCodeList());*/
        param.setSkuList(skuList);
        param.setStartTime(DateUtil.format(startTime, DatePattern.PURE_DATE_PATTERN));
        param.setEndTime(DateUtil.format(endTime, DatePattern.PURE_DATE_PATTERN));
        param.setDateList(dateList);
        return param;
    }

    /**
     * 保存导出操作日志
     *
     * @param type
     * @return OperationLogRecordDTO 操作日志对象
     * @author wuyuanhang
     * @date 2020/11/16
     */
    @Override
    public OperationLogRecordDTO saveOperatorLog(Integer type) {
        OperationLogRecordDTO operationDto = new OperationLogRecordDTO();
        Long userId = UserContext.getUserId();
        operationDto.setOpContent(OperationSourceEnum.THREE_PLAN.getDesc() + "-" + type);
        operationDto.setOpType(OperationTypeEnum.EXPORT.getType());
        operationDto.setOpStatus(OperationStatusEnum.PROCESSING.getType());
        operationDto.setOpSource(OperationSourceEnum.THREE_PLAN.getTableType());
        operationDto.setOpAmount(1L);
        operationDto.setStartTime(new Date());
        operationDto.setEndTime(new Date());
        operationDto.setCreateTime(new Date());
        operationDto.setUpdateTime(new Date());
        operationDto.setCreator(userId);
        operationDto.setCreatorName(userId == 0 ? "system" : UserContext.getApplicationUser().getUsername());
        operationDto.setModifier(userId);
        operationDto.setModifierName(userId == 0 ? "system" : UserContext.getApplicationUser().getUsername());
        return operationLogRecordFacade.insertOperationLog(operationDto);
    }

    /**
     * 异步导出Excel方法
     *
     * @param operationDto          操作日志对象
     * @param excelExportDTO        查询过滤条件
     * @param type                  导出类型-1导出当前页-2导出选中数据-3导出所有数据
     * @param channelPermissionList 渠道权限
     * @param channelPermissionList 区域权限
     * @author wuyuanhang
     * @date 2020/11/16
     */
    @Async
    @Override
    public void createExportFile(OperationLogRecordDTO operationDto, ExcelExportDTO excelExportDTO, Integer type, List<String> channelPermissionList, List<SalesPlanSelectDTO> areaPermissionList, Long userId) {
        Integer opStatus = OperationStatusEnum.SUCCESS.getType();
        String ext = "";
        try {
            ExcelExportDataDTO excelExportDataDTO = this.exportExcel(excelExportDTO, type, channelPermissionList, areaPermissionList, userId);
            // 指定文件夹
            String folderName = OperationSourceEnum.THREE_PLAN.getTableType().toString();
            //获取文件名
            String fileName = ExcelExportUtil.getFileName(OperationSourceEnum.THREE_PLAN.getDesc(), userId, type);
            //写文件并返回路径
            ext = ExcelExportUtil.exportExcel(excelExportDataDTO, folderName, filePath, fileName);
        } catch (RomeException e) {
            log.error("销售计划导出失败", e);
            opStatus = OperationStatusEnum.FAIL.getType();
            operationDto.setFailReason(e.getMessage());
        } catch (Exception e) {
            log.error("销售计划导出失败", e);
            opStatus = OperationStatusEnum.FAIL.getType();
            operationDto.setFailReason("导出失败");
        }
        operationDto.setExt(ext);
        operationDto.setOpStatus(opStatus);
        operationDto.setOpEndTime(new Date());
        operationLogRecordFacade.updateOperationLog(operationDto);
    }
}
